Forum Discussion

Yvalson's avatar
Yvalson
Frequent Visitor
3 years ago

Use measure on filter dax function

Hello,

 

I'm trying to create a table visual that shows the bottom 3 of table X.

The value this top 3 is derrived from is a measure.

Due to this there are a bunch of 0,0 values that I need to get rid of, because now the bottom 3 are all value 0.0.

I tried using the FILTER function to filter out the 0,0 values, but when placing this measure into the visual it gives an error.

 

DAX filter used is:

Articles Filtered = FILTER(Articles; [Gem. # dagen voorraad voorgaande week (verkoper)] > 0)
 
My train of thought was that the filter function would take the Articles table. Use the DAX function to check which of the rows would output a value higher than 0 with the measure and then only show those rows when putting them into a visual.
 
What is the correct way to do this?
 
I have no access to the dataset correctly so all filtering etc.. has to be done through after the entire dataset is loaded into PBI so I can't use Transform.
Need Help

4 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hey, Yvalson 

     

    Filter returns a table so if you place the measure in a visual that expects a single value, it will result in error. 

     

    refer: https://learn.microsoft.com/en-us/dax/filter-function-dax

     

    try creating a calculated table with your given DAX, just add ALL, 

     

    Filtered Articles =
    FILTER(
    ALL(Articles),
    [Gem. # dagen voorraad voorgaande week (verkoper)] > 0
    )

     

    then create a measure using TOPN dax func. 

     

    refer: https://learn.microsoft.com/en-us/dax/topn-function-dax

     

    • Yvalson's avatar
      Yvalson
      Frequent Visitor
      3 years ago

      I see the problem of the measure used.

      I tried the CALCULATETABLE function in my DAX measure, but this still gives an error.

      The entire Dataset is connected via DirectQuery. Maybe this implicates the situation.

      I am for example not able to use the New table or New column buttons in the ribbon.

       

      The DAX function I created looked like this:

       

      Articles Filtered = 

      CALCULATETABLE(

      Articles; FILTER(

      ALL(

      Articles; [Gem. # dagen voorraad voorgaande week (verkoper)] > 0

      )

      )

      )

      • rubayatyasmin's avatar
        rubayatyasmin
        Community Champion
        3 years ago

        Ah, DQ. Try this instead,

         

        Idea measure:-

        Third Lowest Measure =
        VAR RankedValues =
        ADDCOLUMNS (
        ALL ( Articles ),
        "RankedValue", [Gem. # dagen voorraad voorgaande week (verkoper)]
        )
        VAR NonZeroValues =
        FILTER ( RankedValues, [RankedValue] > 0 )
        VAR ThirdLowestValue =
        TOPN (
        3,
        NonZeroValues,
        [RankedValue],
        ASC
        )
        RETURN
        MINX ( ThirdLowestValue, [RankedValue] )

         

        this should give you an idea to solve the problem.

         

        if this doesn't work, can you share the demo file?