cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saxon10
Post Prodigy
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
Seanan
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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

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

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/

@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 

 

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



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

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



@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
Continued Contributor
Continued Contributor

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)

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



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

 

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



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

Seanan
Continued Contributor
Continued Contributor

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

@MFelix,

 

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

You are welcome.  No you cannot.


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

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.

@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
Continued Contributor
Continued Contributor

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. 

@Seanan, thanks for your reply and response. 

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

Seanan
Continued Contributor
Continued Contributor

Hi @Saxon10 

This link should work here

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors