Add a flag column to show/not show records

[mvgust]

Based on the below example, I need to create a flag column (show flag).  I don't know if it makes a difference but Total Hours and Union Hours are measures. Union Time Flag is a conditional column I added to a query in power editor. Work code is something I need to provide in the table visual but based on the union time flag I don't think that needs to be part if the conditional logic? 

Employee	Work Code	Union Time Flag	Total Hours	Union Hours	Show Flag
Alley, Tom	OVE	Other	10		don’t show
Alley, Tom	PTB	Other	9		don’t show
Alley, Tom	RW	Other	151		don’t show
Barger, John	RW	Other	160		don’t show
Carter, Justin	OD	Other	5		show
Carter, Justin	OJ	Other	12		show
Carter, Justin	RW	Other	4		show
Carter, Justin	UBP	Union Time	156	156	show
Delgado, Jessie	OVI	Other	44		don’t show
Delgado, Jessie	RW	Other	144		don’t show
Donovan, Greg	OD	Other	13.8		show
Donovan, Greg	ODI	Other	35.4		show
Donovan, Greg	OJ	Other	14		show
Donovan, Greg	OJI	Other	12.5		show
Donovan, Greg	RW	Other	59.5		show
Donovan, Greg	RWO	Other	42.5		show
Donovan, Greg	V	Other	8		show
Donovan, Greg	UBP	Union Time	42	42	show
Donovan, Greg	UX	Union Time	8	8	show
Harbert, Kevin	OJ	Other	59.7		don’t show
Harbert, Kevin	RW2	Other	144		don’t show
Harbert, Kevin	RW8	Other	96		don't show

[v-janeyg-msft · Accepted solution]

Hi, @mvgust 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure, then use it in filter pane.

Like this:

Measure 2 =
VAR a =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Employee] ),
        FILTER ( ALL ( 'Table' ), [Union Hours] <> BLANK () )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Employee] ) IN a, "show", "don't show" )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[westwrightj]

Hey @mvgust 

   I think I understand what you are trying to do. Could you give more details on when the "don't show" or "show" would be selected?

   What is the business rule that defines if you want to show or not show a certain row?

[mvgust]

The basic logic is if an employee has union hours, I need to show ALL their hours in the table visual. If an employee doesn't have union hours, I need them excluded from the table visual.

[westwrightj]

Ah! Yes I think I fully understand what you are trying to do now. Thanks for clearing that up.

So, you can make a calculated column like this

Show Flag = 

var TheMeasurement = CALCULATE([Union Hours Measure], ALLEXCEPT('Table', 'Table'[Employee]))

return

IF(TheMeasurement > 0, "Show", "Don't Show")

This will reproduce the flag you were looking for in your example

From here just include that flag in the filter pane and you should be good to go

[mvgust]

I'll be using the filter on a table visual.  I will only be showing the records that are marked "show".