topic Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch in DAX Commands and Tips

Count measure results based on conditions/Create a Table based on measure results to make a donut ch

Anonymous — Mon, 15 Aug 2022 13:51:01 GMT

Basically, title. There is a measure that was supposed to serve as a conditional formatting based on values one of the measure's.

the end code (formatted for privacy reasons) as follows:

I would like to count how many values were labeled as "FlagLow", "FlagHigh" and "FlagMedium" to put them into Donut Chart.
I was thinking of putting them into table, but I don't know how do I put the results of SWITCH() function into the table either.

Somebody has an idea how do I get the count of 'flags"?

Thank you

Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch

tamerj1 — Mon, 15 Aug 2022 14:05:58 GMT

Hi Anonymous
You need to create a disconnected filter table containing the three flag values. DAX will take care of the rest but you need to specify this counting will be based on what? In oter words how many times per what? Per date? Per customer? Per product? What were you slicing by before counting?

Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch

Anonymous — Mon, 15 Aug 2022 15:23:49 GMT

Situation:
The counting should be based on 'Project Name' column. So at the end it should be somthing like:
- How many projects are there with "FlagHigh"?
- How many are with "FlagRed"?
- "FlagMedium"?

Could you elaborate on disconnected filter table, please? I am new to this stuff...

Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch

tamerj1 — Mon, 15 Aug 2022 15:41:24 GMT

Hi Anonymous
The disconnected filter table is a table that contains all the unique statuses (the 3 flags) you can create it in excel along with numeric sorting column (required only to sort the flag column in the visual in the desired order) or you can use the folowing code to create a calculated table using DAX

Fiter Table = VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, 3, 1 ), "Sort", [Value] ) VAR T2 = ADDCOLUMNS ( T1, "Flag", SWITCH ( [Sort], 1, "FlagHigh", 2, "FlagMedium", 3, "FlagRed" ) ) RETURN T2

This table has NO RELATIONSHIPS with any other table in your model. You just need to use it to slice by in your visuals, for example to place it in the rows of a matrix visual or use it in a slicer.

Once this table is created, place the [Flag] column in a table visual then place the following measure along with it.

Flag Count = VAR CurrentFlag = SELECTEDVALUE ( 'Filter Table'[Flag] ) VAR T1 = ADDCOLUMNS ( VALUES ( TableName[Project Name] ), "@Flag", [MeasureName] ) VAR T2 = FILTER ( T1, [@Flag] = CurrentFlag ) RETURN COUNTROWS ( T2 )

Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch

Anonymous — Mon, 15 Aug 2022 15:43:31 GMT

I will try it and see whether it'll work, will let you know, THANK YOU!

Re: Count measure results based on conditions/Create a Table based on measure results to make a donut ch

Anonymous — Tue, 16 Aug 2022 07:08:49 GMT

You are godlike. Thank you, it worked as expected.

But there is one thing that bothers me. I've added the last flag which explains "No Next Phase" of the project, I got it into measure of conditional formatting. But it doesn't do anything, and its pard does not appear on the chart.

Long story short: if project has "No Next Phase" text value, it should be labeled with "FlagBlack" and count how many of them are there. I did everything that's supposed to be reasonable but it didn't work!