Count False number of multiple columns and display it from high to low

[Jukotik]

Hi everyone,

It's been couple of days I'm searching for an answer on this forum but can't find any.

I have a table with this type of data inside it :

X	Y	Z	P
True	True	True	False
True	True	True	True
False	False	True	True
True	True	False	False

My final goal would be to have this display on the dashboard :

P	2
X	1
Y	1
Z	1

It is a table showing from high to low the number of false repetitions. I already calculated measure giving me the number of false for each column, but when I try to display it I have this :

So I can't order by high to low because the measures are not connected to each other and even when I transpose it on the matrix visualization it's not working.

I tried different ways but cannot fix my issue until now or find solution in the forum that is really corresponding to my issue 😪

Thanks in advance

[Fowmy]

@Jukotik 

You need to unpivot your data by adding an Index column and write a DAX measure as follows:

Coun of False = CALCULATE( COUNTROWS(Table01), Table01[Value] = FALSE() )

File attached below.

[Jukotik]

Hi thanks a lot for your reply! The problem is that I have other column from other type of data in my table. If I'm doing unpivot it changes all the other data..

[Fowmy]

@Jukotik 

Can you share some sample data with the desired output to have a clear understanding of your question?

You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[Jukotik]

Hey, sorry for the late reply!
For confidentiality reasons I cannot share my data but it really look the same as above.

Warehouse	Date	Login	Question 1	Question 2	...	Question n	Pictures
x	dd/mm/yyyy, hour	name of the person	true	false
x				false
x				false
				true

My table is connected to other tables, also, according to the hour I have calculated which shift is corresponding for example so if I'm doing unpivot it will influence my other formulas.

To calculate for each question the number of false I used this formule :

Question1FalseCount = CALCULATE(COUNTROWS(Table),Table[Question1]=False)

So I would need to have this on my dashboard :

Question 1	Question1FalseCount
...	...
Question n	QuestionnFalseCount

I need the measures to be ordered from highest to lowest. It's the same thing as if I wanted the top 5 values but measures are not connected to each other so I cannot order them. If I'm doing unpivot in a new table it's adding more data and I'm not sure my connections with the date, shift and the other information with still works.

Currently I have these filters on my dashboard so that you have an idea.
Sorry I know it would be easier with my data but I'm not allowed to share it 😕

[v-xinruzhu-msft · Accepted solution]

Hi @Jukotik 

Thanks fo the solution @Fowmy  and @_AAndrade  provided, their solutoin is excellent, but you said you cannot unpivot the columns , so based on their solution, i want to offer some information for you.

Sample data 

1.You can create a new table called it Type.

2.Then create a new measure.

MEASURE =
SWITCH (
    SELECTEDVALUE ( 'Type'[Type] ),
    "P", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[P] = FALSE () ),
    "X", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[X] = FALSE () ),
    "Y", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Y] = FALSE () ),
    "Z", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Z] = FALSE () )
)

3.Put the following field to the matrix visual.

Output

Best Regards!

Yolo Zhu

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

[Jukotik]

Thanks a lot @v-xinruzhu-msft !!! It's solving my issue. Other solutions were interesting also thanks @Fowmy and @_AAndrade for your time!

[_AAndrade]

Hi,

A possible solution is to unpivot your table. Can you unpivot your table to this?:

With a simple measure like this:

SUM = 
CALCULATE(
    COUNT(T_False[Attribute]),
    T_False[Value]="FALSE"
)

The final output should be this, like you're looking for:

[Jukotik]

Hi thanks for your reply! Same answer as above, I have other data in this table like the dates, times, logins etc.. If I'm doing unpivot, it's changing these columns and my other formules depending on this table are not working