Crete measure for multiple columns

[Saxon10]

I have two tables are table 1 and table 2.

In table 1 the following columns are contains description, qty need 10 days, qty need 20 days, qty need 30 days and qty need 40 days. 

In table 2 has days only 4 rows. 

There's no technical connection in between two tables so I don't known how can I link together in order to achieve the result in visualisation. 

Desired result and Example. 

I apply the slicer for table 2 for days so

If I select 10 days then it will show only sum of qty of 10 days and the same thing for rest of the days. 

I would like achieve the result in visualisation. 

I am looking for measure or new calculate column in order to link in between two tables. 

Snapshot of tables and desired result. 

[Seanan · Accepted solution]

Hi @Saxon10 

I have a solution but it is not the most dynamic. If your column headers will never change then this may be a fine solution but I'm sure someone may have a more dynamic option. However, please see the below measure and let me know if this is a viable option.

CheckQty = SWITCH(TRUE(),
    CONTAINSSTRING("Qty Need 10 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 10 Days]),
    CONTAINSSTRING("Qty Need 20 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 20 Days]),
    CONTAINSSTRING("Qty Need 30 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 30 Days]),
    CONTAINSSTRING("Qty Need 40 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 40 Days])) + 0

Result:

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

[Seanan · Accepted solution]

Hi @Saxon10 

You could try this:

Total = 'Items'[10 Days] + 'Items'[20 Days] + 'Items'[30 Days] + 'Items'[40 Days]

Then click on the parameter column and adjust the code to:

Days = {
    ("10Days", NAMEOF('Items'[10 Days]), 0),
    ("20 Days", NAMEOF('Items'[20 Days]), 1),
    ("30 Days", NAMEOF('Items'[30 Days]), 2),
    ("40 Days", NAMEOF('Items'[40 Days]), 3),
    ("Total", NAMEOF('Items'[Total]), 4)
}

Result:

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Saxon10]

@Ashish_Mathur, Thanks for your reply, those qty columns came from dax not part of the data source therefore unable to unpivot the data.

can I get the same output without unpivot the data source.

Please advice 

[MFelix]

Hi @Saxon10 ,

The option given by @Seanan however and with the new parameter fileds you can have a dynamica table that shows all the values directly:

Create a sum measure for each column:

Now create the parameters

Now you can have a dynamic table

If you place it on a card you will have the first one selected also the order you select the values in the slicer is the order of the table:

[Saxon10]

@MFelix, Thanks for your response.

Some reason it's not working properly. 

Example :

If I select 30 days then value not changing its showing 20 days value instead off 30 days. Please refer the snap shot of the error. 

My expectation is if I select 10 days then it will show only 10 days sum of qty the same thing for 20, 30 and 40 days. 

[MFelix]

On the card you need to use the column from the parameter field you used, since it's a card will present the first one selected on the slicer.

[Saxon10]

@MFelix,

I don't have parameters new field option like you have it.

(Highlights in red colour) I don't have any drop down option at my end in order to choose/add the field option.

Is there any setting need to be enabled? Can you please share the step by step article so I can get the same option.

Even I can't see the option your work file. 

How can I add the parameter in card. I am really struggling and its confusing for me.

@Seanan option working only first selection and when I try to choose different days in slicer then sum of qty is blanks.

I got the parameter option at my end(Please refer the snapshot) 

Is there any alternative way I can achieve the desired results. 

Could you please advise. 

Much appreciated your effort and time. 

Thank you 

[Seanan]

Hi @Saxon10 

You can enable field parameters by clicking File -> Options and Settings -> Options -> Preview Features -> Tick field parameter (Thanks @MFelix for showing me this)

[MFelix]

Just adding to this 

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[Saxon10]

@MFelix  and @Seanan. Thanks for sharing the information in order to enable the parameter options.

Now I got the results but when I try to choose all the days then it won't sum up?

[MFelix · Accepted solution]

Hi @Saxon10 ,

For the card create the following measure:

Measure = 
VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( days , Days[Days] , Days[Days Fields]),
        Days[Days]
    )
var SelectedValuesDays = CONCATENATEX(__SelectedValue, Days[Days], "|")

Return
IF(CONTAINSSTRING(SelectedValuesDays, "10"), [10 days]) 
+ IF( CONTAINSSTRING(SelectedValuesDays, "20"), [20 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "30"), [30 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "40"), [40 days])

[Saxon10]

@MFelix, @Seanan, Thank you so much for your support and help. Much appreciated. 

[Seanan · Accepted solution]

Hi @Saxon10 

You could try this:

Total = 'Items'[10 Days] + 'Items'[20 Days] + 'Items'[30 Days] + 'Items'[40 Days]

Then click on the parameter column and adjust the code to:

Days = {
    ("10Days", NAMEOF('Items'[10 Days]), 0),
    ("20 Days", NAMEOF('Items'[20 Days]), 1),
    ("30 Days", NAMEOF('Items'[30 Days]), 2),
    ("40 Days", NAMEOF('Items'[40 Days]), 3),
    ("Total", NAMEOF('Items'[Total]), 4)
}

Result:

[Saxon10]

@MFelix,

Thanks for your response and sorry I missed the steps. I will try and update the feedback to you. 

[Ashish_Mathur]

You are welcome.  No you cannot.

[Seanan · Accepted solution]

Hi @Saxon10 

I have a solution but it is not the most dynamic. If your column headers will never change then this may be a fine solution but I'm sure someone may have a more dynamic option. However, please see the below measure and let me know if this is a viable option.

CheckQty = SWITCH(TRUE(),
    CONTAINSSTRING("Qty Need 10 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 10 Days]),
    CONTAINSSTRING("Qty Need 20 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 20 Days]),
    CONTAINSSTRING("Qty Need 30 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 30 Days]),
    CONTAINSSTRING("Qty Need 40 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 40 Days])) + 0

Result:

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

[Saxon10]

@Seanan,  I try to apply your measure logic in actual data and some reason measure working only qty needs 10 days and rest of them is showing 0 when I try to choose qty need 20 days, 30 days and 40 days I don't know why? 

I have a lot blanks columns in each columns maybe that's the reason it's not calculated properly? 

Can I get new calculate column instead of measure? is that possible?

in your sample data file working without any issues. 

can you please advise.

[Seanan]

Hi @Saxon10 

Thanks for letting me know.

I'll take a look at changing the code to fit in a calculated column. However, I am a little busy today so I'll get back to you later this evening. 

[Saxon10]

@Seanan, thanks for your reply and response. 

[Saxon10]

@Seanan @thanks for your quick reply. Could you please share your working file so I can test and update the feedback to you.

[Seanan]

Hi @Saxon10 

This link should work here