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 ,

According to your description, here's my solution.

1.Create two tables.

Sort Date column by Index column, then make relationship between Date table and other tables with Date column.

2.Create a measure:

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 () )
                )
            ),
            MAX ( '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 () )
                )
            ),
            MAX ( '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 () )
                )
            ),
            MAX ( 'Table3'[Calculated Pass/Fail] )
        )
)

In a matrix, put TableName in Rows, Date in Columns and measure in Values, 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

Wow. This is exactly what I need.  BTW, can I use this with a slicer?  So if I use my slice by date, would this this increase or decrease the amount of columns?

 

Also, i had used a Date Table using Bravo for PowerBI, so I'm having some difficulty adding an Index.  What is the best way to add an index if the date isnt shown in the power query view?