Forum Discussion

Alde's avatar
Alde
Frequent Visitor
4 years ago
Solved

Show a specific column data When no filter is selected

Hi everyone    I'm using this DAX to show a column value from "FEB" with a measure, but I have many measures to adjust every month, instead I want to use a predefied measure "CurrentMonth" to use ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Alde , 

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure = IF(ISFILTERED('Table'[month])=TRUE(),FIRSTNONBLANK('Table'[value%],1),CALCULATE(SUM('Table'[value%]),'Table'[month]=SELECTEDVALUE('Table (2)'[date])))

     

    What cause your error?

    The following comparisons are not supported:

      1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
      2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
      3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =TerritoryParameter[TerritoryParameter Value]

    More details: DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

     

    If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Alde , 

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure = IF(ISFILTERED('Table'[month])=TRUE(),FIRSTNONBLANK('Table'[value%],1),CALCULATE(SUM('Table'[value%]),'Table'[month]=SELECTEDVALUE('Table (2)'[date])))

 

What cause your error?

The following comparisons are not supported:

    1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
    2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
    3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =TerritoryParameter[TerritoryParameter Value]

More details: DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed. 

 

If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

  • Alde's avatar
    Alde
    Frequent Visitor
    4 years ago

    Hi Anonymous 

     

    Thanks for the sample, it did work until 2nd level of nested DAX,

     

    When I tried this one, It doesn't work anymore at 3rd level while I'm trying to create a clustered stacked bar, but it works if I just use a value "Value" instead of SELECTEDVALUE().

     

    Do you have any idea? 🥹 

    Thank youuuuuu

    BUD_OB2 = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return if
        (CurrentType="OB YEAR BUDGET",
        if (ISFILTERED('ORDER_TABLE'[MONTH])=truE(), FIRSTNONBLANK('FCST_BUD'[OB (start of month) BUD FY 42],1),
        CALCULATE(Sum('FCST_BUD'[OB (start of month) bud FY 42]), 'FCST_BUD'[Month] = SELECTEDVALUE(Col_Type[CurrentMonth]))
            )
        )
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Alde ,

      I cannot reproduce your issue. Could you please provide your pbix file without privacy information and desired output?  If impossible, colud you please provide enough sample data to reproduce the issue?

       

      Best Regards

      Community Support Team _ Polly

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Alde's avatar
        Alde
        Frequent Visitor
        4 years ago

        Hi Anonymous 

        Here's the PBI sample file and attached an image for my idea.

        Please help, thank youuuuu <33

        https://drive.google.com/file/d/128YEVdMEsiBYfMy-R6J4SowibiA-sXoC/view?usp=sharing

         

        OBActual_OB = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return
            if
            (CurrentType="OB Actual",
            IF  
            (ISFILTERED('ORDER_TABLE'[MONTH])=False(),
            CALCULATE(Sum('obcb'[OB Actual]), 'obcb'[Month] = "mar"),
            FIRSTNONBLANK('obcb'[OB Actual],1)
            )
            )
        )

         

    • Alde's avatar
      Alde
      Frequent Visitor
      4 years ago

      Ahh, I also tried assigning a variable the value using SELECTEDVALUE() function and using it at third level, and changing back and forth of the nests, but still doesn't work unless it's a typed value,