• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Chihiro

    Pivot table memory using appended queries

    No. Power Pivot based on Data model requires data to be stored in cache. Cached data will stay with the workbook. Only way to reduce size, would be to look at your data and see if you can reduce footprint. Data granularity - Do you actually need all the rows in data model. Or can you...
  2. Chihiro

    Connecting Azure Power BI Embedded to MongoDB

    Cross posted. Please read forum rules and update your post. https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
  3. Chihiro

    DAX formula to recalculate average duration between transactions depending on pivot row context

    Not sure if your data is really representative of your issue. Typically speaking this type of calculation requires one of following. 1. Add calculated column, either at Power Query stage or at DAX. Then use the calculated column for down stream calculation. 2. Add table variable to your DAX. To...
  4. Chihiro

    Not able to select unique rows

    Hmm, best bet in your case is to just merge Access level table to IssuedBooks. Since BookInventryDetail has no employee info. You'd connect the two using "Book".
  5. Chihiro

    Not able to select unique rows

    You have multiple path from AccessLevel to BookInventryDetail. That is causing ambiguity in data. How you should model data, will depend on few things... But read through link to understand importance of appropriate data model in BI (PowerBI, Excel Data model) etc...
  6. Chihiro

    DAX issue with PowerPivot based Dashboard

    I assume you have Calendar table that has all dates of given year. 1. You use CALCULATE(Measure,FILTER(ALL(Table),Year([DateColumn])="2024")) to obtain data from all of calendar date based on condition. You can make "2024" variable in Dax. Alternately you'd use ALLEXCEPT() 2. Simple subtraction...
  7. Chihiro

    Blanks in Power Pivot table

    That message has nothing to do with the measure. It's issue between your PowerQuery and loading it to Data model. Check your query dependency.
  8. Chihiro

    PowerBI New Button Slicer (Nov 2023) and adding images

    Hmm, that depends on URL. And if it's interpreted as valid. For test, I used google image search result and used jpeg base64 string as url. And it worked fine...
  9. Chihiro

    Haversine formula not giving expected results

    You should be able to zip PBIX file to upload. Without looking at sample data, bit hard to pinpoint your issue. But from what I understand, you probably shouldn't use relationships between the two tables. Just calculate starting coordinate (i.e. address coordinate). Then you need to calculate...
  10. Chihiro

    Replace values containing specific letters in cells

    Just another way to remove special characters (especially when exact characters are unknown). = Table.AddColumn(PreviousStep, "Required Data", each Text.Select([Current Data],{"A".."z", " "})) Usually requires another step to clean up extra spaces.
  11. Chihiro

    Calculated table for TOP 3 clients

    In DAX calculated tables are only evaluated upon data load. And not when you make changes to slicer selection. If you need it to be interactive, you need significantly more complex set up. Or you can just use built-in filters to limit what's shown. As well, you need to replace ''[Client name]...
  12. Chihiro

    Calculated table for TOP 3 clients

    Are you doing this in Excel Data Model or is it in PowerBI Data model? If I recall Excel data model doesn't support calculated tables. I'd recommend doing most of the operation in PowerQuery stage. If using PowerBI, then you can simply do it like below. Table 2 = VAR _temp = SUMMARIZE (...
  13. Chihiro

    Date as text - can't change format

    You are missing brackets for MIN and MAX. Dates Range = FORMAT(MIN('Semantic FactCommunications'[LetterSentDate]), "MM/DD/YYYY") & " to " & FORMAT(MAX('Semantic FactCommunications'[LetterSentDate]), "MM/DD/YYYY")
  14. Chihiro

    Useful PowerQuery tricks: Chihiro's notes

    14. Various Table/List manipulation Recently had to build process to import customer generated PDF and transform it into format that can be imported into our project management software. While I can't share entire process, here are some useful operations to sanitize PDF. Check if a column in...
  15. Chihiro

    To bring a table only to today' date

    Don't ever filter DimDate table. Time intelligence will not behave correctly when full year dates are not in date dimension table(s). Always apply filter on Fact table. Do this in PQ using "= Date.From(DateTime.FixedLocalNow())" However, I can't really imagine fact table having future dates...
  16. Chihiro

    Counting number of records in a card from a report in a table view

    ? If this is raw data. Then you just need to COUNTROWS().
  17. Chihiro

    Counting number of records in a card from a report in a table view

    I need to know what the underlying table looks like. Can you upload at least the raw data using Excel spreadsheet?
  18. Chihiro

    Counting number of records in a card from a report in a table view

    You need to provide sample data and what your visual shows. I don't know your data model structure and can't help with what info you provided.
  19. Chihiro

    Counting number of records in a card from a report in a table view

    SUMMARIZE requires that you supply following. SUMMARIZE(TableName, Column(s) to group by, "Aggregated column", calculation for aggregation) You don't have aggregate column in your SUMMARIZE.
  20. Chihiro

    Counting number of records in a card from a report in a table view

    Just stick it into card. No need to have it in the table.
  21. Chihiro

    Counting number of records in a card from a report in a table view

    Then you need to use SUMMARIZE() etc to give it same grain as the table visual. And count # of rows of the summarized table. You don't need all the calculated measures. Only sum() of some column to aggregate based on grouping utilized in your table visual. Ex: If table uses columns from...
  22. Chihiro

    Counting number of records in a card from a report in a table view

    Not quite sure what you need. In general you'd use COUNTROWS(). But it will really depend on your filter context and how your DAX measures are constructed. For an example, if you use CALCULATE(SUM(),FILTER(ALL(TABLE),Condition)) type construct, you'll need special handling that mirrors filter...
  23. Chihiro

    Power BI Weekend

    It's different. Power Query and other more advanced controls are only available on desktop. Though I have no idea about the content of what's covered in that class.
  24. Chihiro

    Fetching multiple rows from Table with Condition

    Please give sample that's representative of your actual need. Otherwise, we won't be able to help. I'd recommend uploading sample workbook, with manually crafted desired output.
  25. Chihiro

    PowerPivot -show rolled-up % in pivot

    You don't add the calculation as columns. But as measures. So it's evaluated according to context of visual or pivot table you use those measures in.
Back
Top