Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:  I w...
Conditional Formatting
COUNT
sum
switch
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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 )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!