cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## DAX

Hi, I'm new to DAX and I'm struggling to understand the following:

I have two columns in the same table:

CID (a unique card ID)

UID (a unique user id)

I want to evaluate all instances where a uniquie UID has more than one CID.

In a pivot table this would be straight fiorward but I'm completely stumped in Power BI.

I can do this in  standard table but I would like it as a measure that I can then stitch into a wider measure.

Can anyone help?

Thanks,

Andy

1 ACCEPTED SOLUTION
Employee

Hi @Andy6001 ,

Suppose this is your sample table.

To display those records where each unique UID has more than one CID and count the number of UID which meets such a condition, please refer to below measures:

```count CID =
IF (
CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ) > 1,
CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ),
BLANK ()
)

Count UID =
IF (
[count CID] = BLANK (),
BLANK (),
CALCULATE (
DISTINCTCOUNT ( Sheet9[UID] ),
FILTER ( ALLSELECTED ( Sheet9 ), [count CID] > 1 )
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

Hi @Andy6001 ,

Suppose this is your sample table.

To display those records where each unique UID has more than one CID and count the number of UID which meets such a condition, please refer to below measures:

```count CID =
IF (
CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ) > 1,
CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ),
BLANK ()
)

Count UID =
IF (
[count CID] = BLANK (),
BLANK (),
CALCULATE (
DISTINCTCOUNT ( Sheet9[UID] ),
FILTER ( ALLSELECTED ( Sheet9 ), [count CID] > 1 )
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors