CODEWITHSUNDEEP
countpowerbidaxpowerquery
mrhippo85
mrhippo85

Reputation: 15

DAX - Measure to show count of rows filtered by values and last report run date

Yet another DAX conundrum that I cannot get my head around!

I have a table with issue ID's, percentage values and a corresponding RAG status (Red, Amber, Green) - this report gets appended to every day with the relevant results and then pulled into Power BI e.g.:

Date Issue ID % RAG
08/03/22 1 10% Red
08/03/22 2 98% Green
08/03/22 3 80% Red
08/03/22 4 91% Amber
08/03/22 5 10% Red
08/03/22 6 98% Green
08/03/22 7 80% Red
08/03/22 8 91% Amber
09/03/22 1 25% Red
09/03/22 2 93% Amber
09/03/22 3 95% Amber
09/03/22 4 99% Green
09/03/22 5 94% Amber
09/03/22 6 50% Red
09/03/22 7 60% Red
09/03/22 8 100% Green
10/03/22 1 10% Red
10/03/22 2 98% Green
10/03/22 3 80% Red
10/03/22 4 91% Amber
10/03/22 5 10% Red
10/03/22 6 98% Green
10/03/22 7 80% Red
10/03/22 8 91% Amber

I can figure out the DAX for pulling the number of values of each of the RAG statuses for the whole table:

RED RAG Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "RED")
AMBER RAG Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "AMBER")
GREEN RAG Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "GREEN")

BUT I need the RAG count for the latest data in my main fact table. I could use a filter when using a card visual but would rather have a measure for this. I have tried the following, as I have "Most Recent Data" true or false column in my Date Dimensioning Table, but get an error:

CALCULATE(
    COUNT('Main Fact Table'[RAG Status] = "RED"),
    'Date DIM Table'[Most Recent Data] IN {TRUE()})

To confirm, my Date Dimensioning Table is just used in lieu of the date/time intelligence option in Power BI and consists of a list of dates, columns created in Power Query to split out the date into various useful bits of info like day, week of year etc. etc. and calculated columns, of which [Most Recent Data] is one of them (DAX code Most Recent Data = if (MAX('Main Fact Table'[Run Date]) = 'Date DIM Table'[Date], TRUE(), FALSE()))

So if the latest run date of the report was the 10th March, I would see this in my Date Dimension Table:

Date Most Recent Data
08/03/2022 False
09/03/2022 False
10/03/2022 True

And therefore wish for the output for each measure (count of RED/AMBER/GREEN RAG status) to be as follows:

Date RAG Count
10/03/2022 Red 4
10/03/2022 Amber 2
10/03/2022 Green 2

Where am I going wrong?

Many thanks in advance!

Upvotes: 0

Views: 11489

Answers (1)

mrhippo85
mrhippo85

Reputation: 15

I figured this one out myself:

First, I created three measures (one for each RAG status):

Count Of Red Rag Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "RED")
Count Of Amber Rag Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "AMBER")
Count Of Green Rag Status = CALCULATE(COUNTROWS('Main Fact Table'),'Main Fact Table'[RAG Status] = "GREEN")

Then I filtered this using the CALCULATE function and my Most Recent Data calculated column in my Date Dimension Table (created by using Most Recent Data = if (MAX('Main Fact Table'[Run Date]) = 'Date DIM Table'[Date], TRUE(), FALSE()):

CALCULATE(
    [Count of RED RAG Status],
    'Date DIM Table'[Most Recent Data] IN { TRUE }

Upvotes: 0

Related Questions