cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PreacherBaby
Helper III
Helper III

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

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:

Untitled.png

 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

1 ACCEPTED SOLUTION

Hi @PreacherBaby 
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 )

View solution in original post

5 REPLIES 5
PreacherBaby
Helper III
Helper III

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...

Hi @PreacherBaby 
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 )

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!

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

tamerj1
Super User
Super User

Hi @PreacherBaby 
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?

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.