cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peterg417 Frequent Visitor
Frequent Visitor

Count entries in 2 different columns

I'm wondering how I would go about calculating this. I want to know how many properties we have Parking Rate information for, regardless of whether it's reserved parking rates or unreserved.

 

In the chart below I have 10 properties. Some have info for 1 column or the other, or both. However, counting and suming both columns will give me an inaccurate result, making it seem like we have 80%, when in reality it is 60%. How would I go about calculating this without double counting in certain situations?

 

Unreserved ParkingReserved Parking
$10$15
 $20
  
$14$20
$10 
  
$18 
  
$20 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Count entries in 2 different columns

Hi @peterg417

 

Try this where Table1 is the table you show:

 

 

Measure =
COUNTROWS (
    FILTER (
        Table1,
        OR (
            Table1[Unreserved Parking] <> BLANK (),
            Table1[Reserved Parking] <> BLANK ()
        )
    )
)

 

to count the number of properties that have info on either type of parking or this measure to calculate the percentage you talk about:

 

Measure2 =
DIVIDE (
    COUNTROWS (
        FILTER (
            Table1,
            OR (
                Table1[Unreserved Parking] <> BLANK (),
                Table1[Reserved Parking] <> BLANK ()
            )
        )
    ),
    COUNTROWS ( Table1 )
)

Code formatted with   www.daxformatter.com

 

 

2 REPLIES 2
Highlighted
Super User
Super User

Re: Count entries in 2 different columns

Hi @peterg417

 

Try this where Table1 is the table you show:

 

 

Measure =
COUNTROWS (
    FILTER (
        Table1,
        OR (
            Table1[Unreserved Parking] <> BLANK (),
            Table1[Reserved Parking] <> BLANK ()
        )
    )
)

 

to count the number of properties that have info on either type of parking or this measure to calculate the percentage you talk about:

 

Measure2 =
DIVIDE (
    COUNTROWS (
        FILTER (
            Table1,
            OR (
                Table1[Unreserved Parking] <> BLANK (),
                Table1[Reserved Parking] <> BLANK ()
            )
        )
    ),
    COUNTROWS ( Table1 )
)

Code formatted with   www.daxformatter.com

 

 

peterg417 Frequent Visitor
Frequent Visitor

Re: Count entries in 2 different columns

Thank you!!

 

I can't verify whether the numbers are correct, but my guess is that they are since they're much lower than when I tried to do it (and the logic in your code makes sense), so I assume that's correct.