cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luis_Gomez Frequent Visitor
Frequent Visitor

HASONEVALUE() not working inside a Table definition formula

Task:

To show top 5 products out of 100s,  user can select a period with a slicer.

 

So I create a table using the following DAX formula

 

TablaTop = filter(

                   SUMMARIZE(Historia,Historia[Marca],Historia[Tema],Historia[Año],"Dato",SUM(Historia[Dato])),

                   Historia[Tema]="PDM"

                   &&

                   Historia[Año]=IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año])))

 

There is, additionaly, a calculated column to distinguish the top 5 from the others

 

Top = IF(RANKX(TablaTop,TablaTop[Dato],,DESC,Skip)<=5,TablaTop[Marca],"Otros")

 

Tables related:

Historia is read from excel. Has several years of data.

Años is a one column table that is derived from Historia from Query editor.

 

let
    Source = Historia,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Año"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Año", Order.Ascending}})
in
    #"Sorted Rows"

 

 

 

If I put IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año]))) in a measure and put the result in a card visualization it shows the correct result the measure reacts to changes from a slicer with 'Años'[Año] in it. So it works.

 

The issue:

 

Table TablaTop does not refreshes at all to slicer selection.

 

If I replace IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año])))  in TablaTop definition with a valid year number the results are perfect.

 i.e.

TablaTop = filter(

                   SUMMARIZE(Historia,Historia[Marca],Historia[Tema],Historia[Año],"Dato",SUM(Historia[Dato])),

                   Historia[Tema]="PDM" &&

                   Historia[Año]=2016)

 

This works

 

 

Need help to resolve this issue. Any alternative formula is accepted.

 

Thank you in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: HASONEVALUE() not working inside a Table definition formula

@Luis_Gomez

 

hi, please try this

 

Change your table Top to: 

 

TablaTopVV=
filter(SUMMARIZE(Historia;Historia[Marca];Historia[Tema];Historia[Año];"Dato";SUM(Historia[Dato]));Historia[Tema]="PDM")

 

Create a calculated Column:

 

TOP = if(RANKX(FILTER(TablaTopVV,TablaTopVV[Año]=EARLIER(TablaTopVV[Año])),TablaTopVV[Dato])<=5,TablaTopVV[Marca],"OTROS")

 

 

A new Measure:

 

ElDato = CALCULATE(SUM(TablaTopVV[Dato]),FILTER(TablaTopVV,TablaTopVV[Año]=[Año seleccionado]))

 

 

Finally in Visual Chart:

 

Rank.png

 

Ready!!

 

 

 

 




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

Proud to be a Datanaut!




View solution in original post

9 REPLIES 9
dkay84_PowerBI New Contributor
New Contributor

Re: HASONEVALUE() not working inside a Table definition formula

Just curious, why not try adding a filter (page or visual level) and setting it for TopN?  Then your table will show the top 5 and, provided the relationship is established between Years and the fact table, it should slice it appropriately.

Luis_Gomez Frequent Visitor
Frequent Visitor

Re: HASONEVALUE() not working inside a Table definition formula

Thank you.

 

The issue is that it is requiered to include the rest of the data as "Others", so for example, calculating % over Total will include the Top 5 and the rest of the data as well.

 

Formulas work well when I hard coded the month number, just need to be dynamic. Data is read on a monthly basis and it is not nice to change hard coded formulas every month. Easy to forget!!!.

 

Pie 2.PNG

 

 

 

dkay84_PowerBI New Contributor
New Contributor

Re: HASONEVALUE() not working inside a Table definition formula

So essentially your question is how to pass in the slicer value for time period to your calculated table instead of hard coding it?

Can you share some sample data?
Luis_Gomez Frequent Visitor
Frequent Visitor

Re: HASONEVALUE() not working inside a Table definition formula

Exactly,

 

I'll will work a little to strip sensitive data and create a sample in order to upload it.

 

Thank you.

v-huizhn-msft Super Contributor
Super Contributor

Re: HASONEVALUE() not working inside a Table definition formula

Hi @Luis_Gomez,

Have you resolved your issue? Where is your sample data? 

In addition, you can create a measure to show the value in slicer.

selected value=CALCULATE(Max(Table[data]),ALLSELECTED(Table))


Best Regards,

Angelia

Luis_Gomez Frequent Visitor
Frequent Visitor

Re: HASONEVALUE() not working inside a Table definition formula

Hi,

 

I have not resolved it yet.

 

I am attaching a link to download a stripped down page, isolating the issue.

 

Your help is greatly appreciated.

 

Regards

 

https://1drv.ms/u/s!AvtcxjkBouSbqk6Wg_E82PODDz3U

 

 

Luis_Gomez Frequent Visitor
Frequent Visitor

Re: HASONEVALUE() not working inside a Table definition formula

Hi, I posted a reply with a link to a pbix file to doanload.

 

Regards

Vvelarde Super Contributor
Super Contributor

Re: HASONEVALUE() not working inside a Table definition formula

@Luis_Gomez

 

hi, please try this

 

Change your table Top to: 

 

TablaTopVV=
filter(SUMMARIZE(Historia;Historia[Marca];Historia[Tema];Historia[Año];"Dato";SUM(Historia[Dato]));Historia[Tema]="PDM")

 

Create a calculated Column:

 

TOP = if(RANKX(FILTER(TablaTopVV,TablaTopVV[Año]=EARLIER(TablaTopVV[Año])),TablaTopVV[Dato])<=5,TablaTopVV[Marca],"OTROS")

 

 

A new Measure:

 

ElDato = CALCULATE(SUM(TablaTopVV[Dato]),FILTER(TablaTopVV,TablaTopVV[Año]=[Año seleccionado]))

 

 

Finally in Visual Chart:

 

Rank.png

 

Ready!!

 

 

 

 




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

Proud to be a Datanaut!




View solution in original post

Highlighted
Luis_Gomez Frequent Visitor
Frequent Visitor

Re: HASONEVALUE() not working inside a Table definition formula

Great!!!

 

 @VvelardeThank you very much!!!

 

Could you please explain the logic of the solution.

 

Regards

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 54 members 1,063 guests
Please welcome our newest community members: