Apply Slicer to Calculate table

[Anonymous]

Hi!

I am struggling with this problem here.
Based on a slicer date range I need to create a summary table which will represent the last state of a given item (book)
Plus, I'd like to apply some other static condition to my CALCULATETABLE command.
Slicer is based on the other table with dates.
What am I doing wrong? My DAX query is below

[Anonymous · Accepted solution]

Hi @Manal_Edd ,

Calculated tables are only recalculated if any of the tables they pull data from are refreshed or updated, so we are not able to use the checked slicer value in the calculated table. I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below:

Flag = 
VAR _selbid =
    SELECTEDVALUE ( 'Table'[book_id] )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[book_id] = _selbid )
    )
VAR _maxstate =
    CALCULATE (
        MAX ( 'Table'[state] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[book_id] = _selbid
                && 'Table'[date] = _maxdate
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[state] ) = _maxstate, 1, 0 )

2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi @Anonymous

Thanks for your elegant solution. Looks like my question was not complete. In essence, I need to convert that table into a chart where every bar will represent a given group. And colour - distribution of the books by the latest state (level of completeness)

With this Flag I am getting only one bar/record on the chart.

If this is possible, could you elaborate your example to do that, please?

Thank you!

[amitchandak]

@Anonymous , A calculated table can not use slicer value, You need a measure. refer to my blog for the same

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e