Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Boolean Calculation

Hello Team,
I would like to convert the below IFELSE statement to DAX Calculated column (Boolean data type) so that I will use in filter and set that to TRUE. COuld you help me with that.

 

If table1.column1='A' then table2.date between table1.startdate and table2.endate
elseIf table1.column1='B' then table2.date between table1.startdate and table2.endate
end

 

table1 and table2 joined on the common field ID

  • This might work. I returned 1 or 0. I would not return TRUE or FALSE. You can return those text values, but I would shy away from returning a true boolean value in DAX. It can cause issues with getting some code to work. I always convert to 1/0 or text values "T" or "F" or whatever you want.

    Some Column =
    IF(
        table1.[column1] = "A",
        IF(
            RELATED( table2.[date] ) >= table1.[startdate]
                && RELATED( table2.[date] )
                    <= RELATED( table2.[enddate] ),
            1,
            0
        ),
        IF(
            table1.[column1] = "B",
            IF(
                RELATED( table2.[date] ) >= table1.[startdate]
                    && RELATED( table2.[date] )
                        <= RELATED( table2.[endate] ),
                1,
                0
            )
        )
    )
    

    It is also only going to work if the tables are related through a common dimension table, and even then, this is something that is best done in Power Query or the data source via a merge with a comparison. Cannot say for sure as you included no data.

     

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

3 Replies

  • edhans's avatar
    edhans
    Community Champion

    This might work. I returned 1 or 0. I would not return TRUE or FALSE. You can return those text values, but I would shy away from returning a true boolean value in DAX. It can cause issues with getting some code to work. I always convert to 1/0 or text values "T" or "F" or whatever you want.

    Some Column =
    IF(
        table1.[column1] = "A",
        IF(
            RELATED( table2.[date] ) >= table1.[startdate]
                && RELATED( table2.[date] )
                    <= RELATED( table2.[enddate] ),
            1,
            0
        ),
        IF(
            table1.[column1] = "B",
            IF(
                RELATED( table2.[date] ) >= table1.[startdate]
                    && RELATED( table2.[date] )
                        <= RELATED( table2.[endate] ),
                1,
                0
            )
        )
    )
    

    It is also only going to work if the tables are related through a common dimension table, and even then, this is something that is best done in Power Query or the data source via a merge with a comparison. Cannot say for sure as you included no data.

     

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

    • edhans's avatar
      edhans
      Community Champion

      Glad I was able to assist Anonymous