cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Crete measure for multiple columns

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.

3 ACCEPTED SOLUTIONS
Solution Supplier

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.

Solution Supplier

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:

Super User

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])
``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

21 REPLIES 21
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Prodigy

@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.

Super User

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:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Prodigy

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.

Super User

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.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Prodigy

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.

Much appreciated your effort and time.

Thank you

Solution Supplier

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)

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Prodigy

@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?

Super User

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])
``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Prodigy

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

Solution Supplier

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:

Post Prodigy

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

Super User

You are welcome.  No you cannot.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Supplier

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.

Post Prodigy

@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.

Solution Supplier

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.

Post Prodigy

Post Prodigy

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

Solution Supplier

Hi @Saxon10

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors