Forum Discussion

mmar141's avatar
mmar141
Frequent Visitor
3 years ago
Solved

Help with adding columns

Hello, I'm a very new user to power bi (about 2 weeks) and am running into some issues with my first report.  I have 3 tables that all calculate a pass / fail column if certain conditions are met....
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi mmar141 ,

    If you mean the data type in Table1, Table2 and Table3 is Date, but in the new created table is text, here's my solution.

    1.Delete the relationships betwen the four tables.

    2.Modify the measure to:

    Measure =
SWITCH (
    MAX ( 'Table'[TableName] ),
    "Table1",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table1' ), 'Table1'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table1' ), 'Table1'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table1', 'Table1'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table1'[Calculated Pass/Fail]
            )
        ),
    "Table2",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table2' ), 'Table2'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table2' ), 'Table2'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table2', 'Table2'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table2'[Calculated Pass/Fail]
            )
        ),
    "Table3",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table3' ), 'Table3'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table3' ), 'Table3'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table3', 'Table3'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table3'[Calculated Pass/Fail]
            )
        )
)

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

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

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi mmar141 ,

Please check if the Date columns are all in the same data type. I guess some is text and others are date.

 

Best regards,

Community Support Team_yanjiang

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

mmar141's avatar
mmar141
Frequent Visitor
3 years ago

The data type for my type was indeed text.  However, I cant change the data type column to Text because I added a percentage in one of the rows.  How would I resolve this?  Also, my real report also does not include a %?  How do i add a percentage row to a query that I load from SQL?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi mmar141 ,

    If you mean the data type in Table1, Table2 and Table3 is Date, but in the new created table is text, here's my solution.

    1.Delete the relationships betwen the four tables.

    2.Modify the measure to:

    Measure =
SWITCH (
    MAX ( 'Table'[TableName] ),
    "Table1",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table1' ), 'Table1'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table1' ), 'Table1'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table1', 'Table1'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table1'[Calculated Pass/Fail]
            )
        ),
    "Table2",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table2' ), 'Table2'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table2' ), 'Table2'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table2', 'Table2'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table2'[Calculated Pass/Fail]
            )
        ),
    "Table3",
        IF (
            MAX ( 'Date'[Date] ) = "%",
            DIVIDE (
                COUNTROWS ( FILTER ( ALL ( 'Table3' ), 'Table3'[Calculated Pass/Fail] = "P" ) ),
                COUNTROWS (
                    FILTER ( ALL ( 'Table3' ), 'Table3'[Calculated Pass/Fail] <> BLANK () )
                )
            ),
            MAXX (
                FILTER ( 'Table3', 'Table3'[Date] = CONVERT ( MAX ( 'Date'[Date] ), DATETIME ) ),
                'Table3'[Calculated Pass/Fail]
            )
        )
)

    Get the correct result:

    I attach my sample below for your reference.

     

    Best regards,

    Community Support Team_yanjiang

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

    • mmar141's avatar
      mmar141
      Frequent Visitor
      3 years ago

      Thank you so much for this.  I think I learned more from you than I did in the last 2 weeks by myself.  One last question, is it possible to calculate the % without adding % into the date row? 

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi mmar141 ,

        I'm glad you learned from here, it's my pleasure! I think we can't do that without a custom column, because for the Total value, customization is not supported.

         

        Best regards,

        Community Support Team_yanjiang