Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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(Histor...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Anonymous

     

    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:

     

     

    Ready!!

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

 

 

 

dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
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?
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Anonymous,

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

     

    Regards

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Anonymous

       

      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:

       

       

      Ready!!

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Great!!!

         

         VvelardeThank you very much!!!

         

        Could you please explain the logic of the solution.

         

        Regards