Forum Discussion
Build Table from Calculated Measures
Hi Anonymous
If you hope to have the table interact with a slicer on the page, having everything including measures into a DAX table (calculated table) is not a good idea. This is because that calculated tables cannot be interact with slicers. To have interactive results, you need to create measures. So you need to have separate measures for Result and Icon.
Example:
1. Create a measure for Result to pick the specific measure for each heading name:
For the same Result measure, click Format dropdown box and select Dynamic. Then create dynamic format with a DAX formula like below.
2. Create a measure for Icon. You can define different criteria for each measure to select URLs.
Then you will have a table visual like below. Each measure may have different format.
For the Mesure heading table, you can use Enter data to create it easily.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
HI Dangar332 ,
I'm afraid I can't share any data. But here's what I'm looking for:
- I have 3 separate measures, Total Active Clients, Total New Clients and Community of Clients
- For each of these, I have another measure that displays an Image URL depending on the result. These measure names would be Total Active Clients Vs Target - Icon, for example
- In the table, I want to have 3 columns:
- Headline Measure Name - this would be text entered such as "Total Active Clients". There's no measure for these
- Result - this would show the relevant measure for each row
- Icon - this would show the relevant icon measure for each row
Any ideas on how I can achieve this? I've seen an example where I create a simple table with references, like this:
HeadlineMeasureTable =
DATATABLE(
"Report Page", STRING,
"Headline Measures", STRING,
"MeasureKey", STRING,
"Order", DOUBLE,
{
{ "Client Reach", "Total Active Clients", "TotalActiveClients", 1},
{ "Client Reach", "Total New Clients", "TotalNewClients", 2},
{ "Client Reach", "Community of Clients", "CommunityOfClients", 3},Then I would have to create a measure that references this lookup table, like this:
Result =
SWITCH(
SELECTEDVALUE(HeadlineMeasureTable[MeasureKey]),
"TotalActiveClients", FORMAT([Total Active Clients], "0,0"),
"TotalNewClients", FORMAT([Total New Clients], "0,0"),
"CommunityOfClients", FORMAT([Total Community of Clients], "0,0"),
But this means I need to create separate measures for each column, which could get a bit tricky to manage. Ideally I'd like to have everything managed in one DAX table.
Thanks,
Mark
Hi Anonymous
If you hope to have the table interact with a slicer on the page, having everything including measures into a DAX table (calculated table) is not a good idea. This is because that calculated tables cannot be interact with slicers. To have interactive results, you need to create measures. So you need to have separate measures for Result and Icon.
Example:
1. Create a measure for Result to pick the specific measure for each heading name:
For the same Result measure, click Format dropdown box and select Dynamic. Then create dynamic format with a DAX formula like below.
2. Create a measure for Icon. You can define different criteria for each measure to select URLs.
Then you will have a table visual like below. Each measure may have different format.
For the Mesure heading table, you can use Enter data to create it easily.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi Anonymous ,
That's pretty much what I'm doing. The only difference is I'm using the FORMAT function within the Measure section rather than converting the Format to Dynamic.
Thanks,
Mark