Reply
Saxon10
Post Prodigy
Post Prodigy
Partially syndicated - Outbound

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
Seanan
Solution Supplier
Solution Supplier

Syndicated - Outbound

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:

NVIDIA_Share_mxPjKvIRJ2.png

 

 

 

 

 

 

 

 

 

 

NVIDIA_Share_oEuiuo9Qxv.png

 

 

 

 

 

 

 

 

 

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

View solution in original post

Seanan
Solution Supplier
Solution Supplier

Syndicated - Outbound

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:

NVIDIA_Share_AD4enHhAyv.png

View solution in original post

Syndicated - Outbound

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

 

MFelix_0-1656666366228.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

21 REPLIES 21
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

@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 

 

Syndicated - Outbound

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:

MFelix_5-1656578631792.png

Now create the parameters

MFelix_0-1656578407693.png

 

MFelix_6-1656579114034.png

 

Now you can have a dynamic table

MFelix_3-1656578489024.png

 

MFelix_7-1656579181638.png

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:

MFelix_8-1656579227639.png

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

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

Syndicated - Outbound

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.

 

MFelix_0-1656600318166.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

@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
Solution Supplier
Solution Supplier

Syndicated - Outbound

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)

Syndicated - Outbound

Just adding to this 

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

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

 

Syndicated - Outbound

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

 

MFelix_0-1656666366228.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

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

Seanan
Solution Supplier
Solution Supplier

Syndicated - Outbound

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:

NVIDIA_Share_AD4enHhAyv.png

Syndicated - Outbound

@MFelix,

 

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

Syndicated - Outbound

You are welcome.  No you cannot.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Seanan
Solution Supplier
Solution Supplier

Syndicated - Outbound

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:

NVIDIA_Share_mxPjKvIRJ2.png

 

 

 

 

 

 

 

 

 

 

NVIDIA_Share_oEuiuo9Qxv.png

 

 

 

 

 

 

 

 

 

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

Syndicated - Outbound

@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
Solution Supplier
Solution Supplier

Syndicated - Outbound

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. 

Syndicated - Outbound

@Seanan, thanks for your reply and response. 

Syndicated - Outbound

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

Seanan
Solution Supplier
Solution Supplier

Syndicated - Outbound

Hi @Saxon10 

This link should work here

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)