Forum Discussion

hmattje's avatar
hmattje
Frequent Visitor
3 years ago
Solved

Challenge - Count most recent rows for each Dataset - filtered by a date slicer

Hi All, 

I need help to create a measure in dax.

My problem is I need to create a bar chart to show Count of "Columns" by "Category" it is simple, but I need to only count columns of latest records for each Dataset according date slicer filter .
As you can see on image below i have a sample of results with different dates slicer .

I can't use distinctcount(Column), because there are different categories with the same column.
Thank you.

Sample File

 

 

Need Help
  • hmattje's avatar
    hmattje
    3 years ago

    Thanks for your reply
    I managed to do it using this measure below

    SUMX(
    ADDCOLUMNS(
        VALUES(Registration[Dataset]),
        "Count",
        VAR vDataMax =
            CALCULATE(
                MAX(Registration[Date]),
                ALLEXCEPT(Registration, Registration[Dataset]), 'Date'[Date] >= MIN('Date'[Date]) && 'Date'[Date] <= MAX('Date'[Date])
            )
        RETURN
        COUNTROWS(
            FILTER(
                SUMMARIZE(
                    Registration,
                    Registration[Date],
                    Registration[Dataset],
                    Registration[Column],
                    Registration[Type]
                ),
                Registration[Date] = vDataMax
            )
        )
    ),
    [Count]
)

     

2 Replies