Help with adding columns

[mmar141]

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.   (see simplified example)

I want to have a main reporting page with a transposed table containing the pass fail metrics along with the percentages like this

I've tried building a new table using SelectColumns/Add Columns, but it keeps giving me error like 
"A single value for column 'Pass_Fail" cannot be determined .  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result"

I'm fairly certain I'm using the wrong approach to get the data together in one place.  Can anyone help?  Would converting pass fail to binary be the correct approach? If so, how would I approach the <Null> values?

[v-yanjiang-msft]

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]

I'm close, but when I add the measure it just displays P?

[v-yanjiang-msft]

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]

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 · Accepted solution]

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]

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]

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

[mmar141]

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?