Tabel with all messures returning defined value

[LukaszLyszczak7]

Dear Community,

I've stuck with one issue. I'm building KPI dashboard to collect figures from 10 plants round the world. I've created dataset excel files based, where the delagates put the data once a month in their own file. To check update status, I've created for each KPI separate messure that checks if for actuall month data has been provided (if field is blank it returns 0 if not 1). I've 54 KPI's to check so I've created 54 such messures, for each one separately, stored in area folders.

I would like to build a table / matrix where I can show the names of all messures returning only 0 to see whitch KPI are not updated. I've search many hours for a solution, unfortunately without success. Thanks for your support.

[some_bih]

Hi @LukaszLyszczak7 then one option to you is to do following

After DAX query view is generated code based on steps above, find your EVALUATE with SUMMARIZECOLUMNS part like

EVALUATE

    SUMMARIZECOLUMNS(

        "Your meaure name", 'Measure_Table'[#DistinctCount],

        "Your meaure name2", 'Measure_Table'[#DistinctCustomer]

)

Then copy this code and Create New table with some your new  table name.

Use this table for matrix.

[LukaszLyszczak7]

Hi @some_bih, was trying to do this way, but still cannot move forward. I copied the code as you proposed, clean up unnecessery stuff, looks like this now:

EVALUATE
SUMMARIZECOLUMNS ( "Sales", '#Measures'[Sales],
"EBITDA", '#Measures'[EBITDA],
"Target_EBITDA", '#Measures'[Target_EBITDA],
"OPEX", '#Measures'[OPEX],
"Target_OPEX", '#Measures'[Target_OPEX],
"Labour cost - direct eployees", '#Measures'[Labour cost - direct eployees],
"Target_Labour cost - direct eployees", '#Measures'[Target_Labour cost - direct eployees],
"Labour cost - indirect eployees", '#Measures'[Labour cost - indirect eployees],
"Target_Labour cost - indirect eployees", '#Measures'[Target_Labour cost - indirect eployees],
"Liquidity planning", '#Measures'[Liquidity planning],
"Target_Liquidity planning", '#Measures'[Target_Liquidity planning],
"Equity", '#Measures'[Equity],
"Target_Equity", '#Measures'[Target_Equity],
"Revenue", '#Measures'[Revenue],
"Target_Revenue", '#Measures'[Target_Revenue],
"Cash allocation", '#Measures'[Cash allocation],
"Target_Cash allocation", '#Measures'[Target_Cash allocation],
"DSO", '#Measures'[DSO],
"Target_DSO", '#Measures'[Target_DSO],
"Open payments debitors", '#Measures'[Open payments debitors],
"Target_Open payments debitors", '#Measures'[Target_Open payments debitors],
"Timely closing", '#Measures'[Timely closing],
"Target_Timely closing", '#Measures'[Target_Timely closing],
"Timely reporting", '#Measures'[Timely reporting],
"Target_Timely reporting", '#Measures'[Target_Timely reporting],
"Overall Equipment Effectiveness (OEE)", '#Measures'[Overall Equipment Effectiveness (OEE)],
"Stock value", '#Measures'[Stock value],
"Stock finished", '#Measures'[Stock finished],
"Stock WIP", '#Measures'[Stock WIP],
"Stock purchased", '#Measures'[Stock purchased],
"Special shipments", '#Measures'[Special shipments],
"On time delivery (OTD)", '#Measures'[On time delivery (OTD)],
"Backlog", '#Measures'[Backlog],
"Supplier on time delivery (OTD)", '#Measures'[Supplier on time delivery (OTD)],
"COPQ", '#Measures'[COPQ],
"Blockstock", '#Measures'[Blockstock],
"Scrap", '#Measures'[Scrap],
"Customer complaints", '#Measures'[Customer complaints],
"Suppliers complaints", '#Measures'[Suppliers complaints],
"Part price variation index", '#Measures'[Part price variation index],
"Target_Part price variation index", '#Measures'[Target_Part price variation index],
"Total Saving performance", '#Measures'[Total Saving performance],
"Target_Total Saving performance", '#Measures'[Target_Total Saving performance],
"Fluctuation", '#Measures'[Fluctuation],
"Headcount", '#Measures'[Headcount],
"Target_Headcount", '#Measures'[Target_Headcount],
"Headcount white collars", '#Measures'[Headcount white collars],
"Headcount blue collars indirect", '#Measures'[Headcount blue collars indirect],
"Headcount blue collars direct", '#Measures'[Headcount blue collars direct],
"Accidents", '#Measures'[Accidents],
"Days without accident", '#Measures'[Days without accident],
"Absence ratio", '#Measures'[Absence ratio],
"Absence - holiday", '#Measures'[Absence - holiday],
"Absence - sick leave", '#Measures'[Absence - sick leave],
"ESG", '#Measures'[ESG],
"SPV", '#Measures'[SPV],
"Won orders", '#Measures'[Won orders],
"Customer satisfaction", '#Measures'[Customer satisfaction],
"Goals acheiving", '#Measures'[Goals acheiving],
"Budget vs plan", '#Measures'[Budget vs plan],
"Change management", '#Measures'[Change management],
"IT Safety", '#Measures'[IT Safety],
"IT Performance", '#Measures'[IT Performance],
"Payment terms improvement (ratio) Group_level", '#Measures'[Payment terms improvement (ratio) Group_level],
"Payment terms improvement (working capital) Group_level", '#Measures'[Payment terms improvement (working capital) Group_level],
"Sourcing Group_level", '#Measures'[Sourcing Group_level],
"Target Payment terms improvement (ratio) Group_level", '#Measures'[Target Payment terms improvement (ratio) Group_level],
"Target Payment terms improvement (working capital) Group_level", '#Measures'[Target Payment terms improvement (working capital) Group_level],
"Target Sourcing Group_level", '#Measures'[Target Sourcing Group_level],
"Contracts HILO_level", '#Measures'[Contracts HILO_level],
"Target Contracts HILO_level", '#Measures'[Target Contracts HILO_level],
"Spend distribution HILO_level", '#Measures'[Spend distribution HILO_level],
"Target Spend distribution HILO_level", '#Measures'[Target Spend distribution HILO_level],
"Suppliers classification", '#Measures'[Suppliers classification] )

In DAX Studio I get proper results runing this code, but honestly have no idea how to put it into new created table. Have tried to repleace automaticly generated code of new table with it - error. Tried format Table = my copied code - error. Can you please advice how to do it?

[some_bih]

Hi @LukaszLyszczak7 

Create New table with your EVALUATE code, above, not in DAX Studio, but in pbi desktop file as any other new table.

Did you try this?

[LukaszLyszczak7]

Hi @some_bih, 

of course I'm trying to create a new table in PBI desktop. DAX studio was used to check whether the EVALUATE query works. Unfortunately, trying to use the above EVALUATE code returns an error. I also tried with single measures, I keep getting an error marked in green saying: "To use special characters in a calculated table name, enclose the entire name in square brackets ([]) and add a ] character to any closing square brackets in the name."

[some_bih]

Hi @LukaszLyszczak7 

then try to 

1. rename measure table and / or measure name not to include "#" sign

2. try everything again 🙂

[some_bih]

Hi @LukaszLyszczak7 

If you need to list all measures is the model, evaluated please follow steps:

Go to DAX query view then select and measure on ... (3 dots) and follow last picture.

Doing this wai you will get data for your measure evaluated.

[LukaszLyszczak7]

HI Some_bih, thanks for you reply. Your proposal shows the results of all messures, but I need it in the dashboard as a table of matrix so I think I cannot utilize this solutions this way. My purpose is to create a matrix where I can see all my messures and the result of it and filtered it out only to the ones returned 0.