count number of values in each column with DAX

[Anonymous]

Hello everyone,

I am trying to count values in each column and get the values into another table. 

this is what I want to achieve:

I have tried several functions, one of them is here.

Daten Prüfung = 
ADDCOLUMNS(SUMMARIZE(Uferbauwerke_validation,
    Uferbauwerke_validation[Bauteilkategorie_check],
    "bau", CALCULATE(COUNT(Uferbauwerke_validation[Bauteilkategorie_check]),FILTER(Uferbauwerke_validation, Uferbauwerke_validation[Bauteilkategorie_check]="bestehende Daten")), 
    "bau_kein", CALCULATE(COUNT(Uferbauwerke_validation[Bauteilkategorie_check]), FILTER(Uferbauwerke_validation, Uferbauwerke_validation[Bauteilkategorie_check]="keine Daten")) ,   
    "material", CALCULATE(COUNT(Uferbauwerke_validation[Material_check]), FILTER(Uferbauwerke_validation, Uferbauwerke_validation[Material_check]="bestehende Daten")),
    "material_kein", CALCULATE(COUNT(Uferbauwerke_validation[Material_check]), Uferbauwerke_validation[Material_check]="keine Daten" )),
    
    
    "total",  COUNT ( Uferbauwerke_validation[Bauteilkategorie_check] ))

But I could not achieve what I really need. Any help would be great! Many thanks in advance!

[Greg_Deckler]

@Anonymous You would likely be better off unpivoting your columns. That said, you could use MC Aggregations:

Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community

[Anonymous]

@Greg_Deckler thanks for the reply.
This solution seems to check values in a row.
However, I need to check the number of values in each column, and adding the results into another table as a summarization.

[Greg_Deckler]

@Anonymous Is this the only table you are working with? Do you have the values you want to count in a separate table? And again, unpivoting your columns would make this child's play. Is that not possible?

[Anonymous]

@Greg_Deckler the table is collection of data which are existing in other source tables. I am not able to work on it in power query, that is why I couldn't apply unpivoting option. Is there any way to summarize values in each column by using DAX? 
at the end, I try to create this table:

[Greg_Deckler · Accepted solution]

@Anonymous You could always UNION a SELECTCOLUMNS of each column and then DISTINCT to get a table of the distinct values in all of the columns.

[Anonymous]

@Greg_Deckler I am not sure if this is what you mentioned but it works somehow 🙂

Daten Prüfung = UNION(
distinct(SELECTCOLUMNS('Uferbauwerke_validation',
"Daten Art", "bestehende Daten",
"bau", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Bauteilkategorie_check]="bestehende Daten"),"bestehende Daten"),
"nutzung", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Nutzung_check]="bestehende Daten"),"bestehende Daten"),
"material", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Material_check]="bestehende Daten"),"bestehende Daten"),
"quartier", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Quartier_check]="bestehende Daten"),"bestehende Daten"),
"zustand", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Zustand_check]="bestehende Daten"),"bestehende Daten"),
"laufende_Nummer", COUNTAX(FILTER(Uferbauwerke_validation,Uferbauwerke_validation[laufende_Nummer_check]="bestehende Daten"),"bestehende Daten")
)),
distinct(SELECTCOLUMNS('Uferbauwerke_validation',
"Daten Art", "keine Daten",
"bau", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Bauteilkategorie_check]="keine Daten"),"keine Daten"),
"nutzung", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Nutzung_check]="keine Daten"),"keine Daten"),
"material", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Material_check]="keine Daten"),"keine Daten"),
"quartier", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Quartier_check]="keine Daten"),"keine Daten"),
"zustand", COUNTAX(FILTER('Uferbauwerke_validation','Uferbauwerke_validation'[Zustand_check]="keine Daten"),"keine Daten"),
"laufende_Nummer", COUNTAX(FILTER(Uferbauwerke_validation,Uferbauwerke_validation[laufende_Nummer_check]="keine Daten"),"keine Daten")
)))