Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

DAX IF statement

  • 1.  DAX IF statement

    Bronze Contributor
    Posted 4 days ago
      |   view attached
    Hi all,

    Can anyone tell me what I am doing wrong here?  I have created a table with 2 columns, "Index" and "Record".  "Index" has values from 1 to 9, and "Record" has the values  1,2,3,1,2,3,1,2,3.  I created a slicer to select by "Record" and a measure as:
          Measure = HASONEVALUE('Table'[Record]).
    I then created a third column as :
          Column = IF([Measure],'Table'[Record],'Table'[Index])
    :
    I expected this to change the value of "Column" depending on whether there was a value selected in the slicer but it always has the "Record" value. The measure is returning the expected values when records are selected and deselected.

    I have attached a couple of examples of this behaviour.

    Thanks and Regards

    Gerry




    ------------------------------
    Gerry Miller
    Oracle DBA
    Brisbane
    ------------------------------

    Attachment(s)

    docx
    IFStatement.docx   32K 1 version
    Academy - Online Interactive Learning from Experts


  • 2.  RE: DAX IF statement

    Silver Contributor
    Posted 4 days ago

    Hi Gerry,
    I think this is because of how the measure is evaluated in the context of each row vs the overall result. You can see this if you add the measure you have created to the table. Without any value selected it is true for each row even though the overall evaluation is false. 

    Rather than use a calculated column, you could use a measure like below. My understanding is that you want to display the index if more than one record is selected and the record if only one record is selected.
    Measure 2 = if(HASONEFILTER('Table'[Record]),max('Table'[Record]),max('Table'[Index]))
    Hope this helps,
    Colin.


    ------------------------------
    colin mcilwain
    Lead Process Specialist
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: DAX IF statement

    Bronze Contributor
    Posted 4 days ago
    Thanks Colin, but that still returns "Record" no matter how many selections are made in the slicer.

    I am trying to figure a way to have a column that shows a count of how many selections have been made for the whole table. If it is 1 then use record otherwise use index.  can't get it though.

    Back to the drawing board

    Gerry

    ------------------------------
    Gerry Miller
    Oracle DBA
    Brisbane
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: DAX IF statement

    Bronze Contributor
    Posted 4 days ago
    ​Ha! this is doing my head in!

    I created another measure that returns the number of distinct records selected:
       Measure 3 = CALCULATE(DISTINCTCOUNT([Record]),ALLSELECTED('Table'))

    Then another that said if Measure 3= then return true:
       Measure 4 = IF([Measure 3]=1,TRUE,FALSE)

    and then a column that said if Measure 4 is TRUE then use the 'Record' value otherwise the 'Index'
      Column = IF([Measure 4]==TRUE(),[Record],[Index])

    and it doesn't work.   The 'Index' value is returned regardless:


    Help!

    Regards

    Gerry


    ------------------------------
    Gerry Miller
    Oracle DBA
    Brisbane
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: DAX IF statement

    Silver Contributor
    Posted 3 days ago
    Hi Gerry,
    It's working for me - if I select one value it returns the record otherwise the index. Are you using 'HASONEFILTER' in Measure 2 rather than 'HASONEVALUE'?​
    Have I misunderstood what you're trying to do?

    Kind regards,
    Colin.


    ------------------------------
    colin mcilwain
    Lead Process Specialist
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: DAX IF statement

    Bronze Contributor
    Posted 2 days ago
    Hi Colin,

    Yes, you do understand what I am trying to do.

    I have uploaded a short video demonstrating the issue, and this shows that I am using HASONEFILTER. I have also uploaded the pbix file, which may give a cue as to what I am doing wrong.

    Regards

    Gerry

    ------------------------------
    Gerry Miller
    Oracle DBA
    Brisbane
    ------------------------------

     Video

    Attachment(s)

    pbix
    IF.pbix   35K 1 version
    Academy - Online Interactive Learning from Experts


  • 7.  RE: DAX IF statement

    Silver Contributor
    Posted 2 days ago
      |   view attached
    Hi Gerry,
    bit of a weird one! It turns out the only difference between our files is that I have the default summarization set to 'Sum' so that when the fields are added to the table mine default to 'Sum' even though it doesn't display 'Sum of Record' in the field list of the table.

    This affects how the measure is evaluated in the row context but doesn't affect how the data is displayed as the records are unique.


    Try changing the Record field in the table from 'Don't summarize' to 'Sum'. Hopefully this won't cause any other issues with your data display.
    I have modified the pbix and attached.

    Kind regards,
    Colin.


    ------------------------------
    colin mcilwain
    Lead Process Specialist
    ------------------------------

    Attachment(s)

    pbix
    IF.pbix   35K 1 version
    Academy - Online Interactive Learning from Experts


  • 8.  RE: DAX IF statement

    Bronze Contributor
    Posted yesterday
    Hi Colin,

    A bit of weird one for sure.  Finally relieved to get a solution.

    Many Thanks

    Gerry

    ------------------------------
    Gerry Miller
    Oracle DBA
    Brisbane
    ------------------------------

    Academy - Online Interactive Learning from Experts