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

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