Build Table from Calculated Measures

[Mark_Holland_GD]

Hi,

I want to create a table from calculated measures that sit across multiple tables. Here's the result I'm trying to get to:

Here are some points to note:

• All the information under the Headline Measure Name column would be text entered when creating the table - they don't exists as measures
• For the Result column, there are 3 separate measures referenced here, the values of which will be different - from currency to percentage, etc
• For the Icon column, the measures are Image URLs that show a different Icon depending on the results

The table needs to interact with a slicer I have on the page.

How can I achieve this?

Thanks,

Mark

[Mark_Holland_GD]

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

[Anonymous · Accepted solution]

Hi   @Mark_Holland_GD 

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!

[Mark_Holland_GD]

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

[Dangar332]

Hi, @Mark_Holland_GD 

It would be better if you provide some samle data, but remove sensitive information.