Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Jameel
Regular Visitor

Common Vs uncommon

I have two excel.One contain 12 ID no, Title and another excel contain 5 title.

I need to create a dax function that compares both titles and if it matches it should count as common and if not matches it should count as uncommon.

i need to visualise in pie chart

 

i used below calclated column:

matchstatus =
IF(
   ISEMPTY(FILTER('excel1','excel1'[Title] = 'excel2[Title])),
   "Uncommon",
   "common"
)
But instead of uncommon, it is showning blank in pie chart.

.

 

Can anyone help?excel1excel1excel2excel2

1 ACCEPTED SOLUTION

Hi @Jameel ,

 

According to your statement, I think your issue should be caused that you add this column in Sheet1(2). Due to it doesn't contain all title in it, so it will return blank in your visual. Please add a column in Sheet1.

Column =
IF (
    ISEMPTY (
        FILTER ( 'Sheet1 (2)', 'Sheet1 (2)'[title] = EARLIER ( 'Sheet1'[title] ) )
    ),
    "UnCommon",
    "Common"
)

Result is as below.

vrzhoumsft_0-1701153143032.pngvrzhoumsft_1-1701153194695.png

 

Best Regards,
Rico Zhou

 

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

 

View solution in original post

9 REPLIES 9
Jameel
Regular Visitor

@Fowmy Thanks for the help

Fowmy
Super User
Super User

@Jameel 

In your Excel 1 Table, aa new Calculated column with this DAX:

IF( ISEMPTY( FILTER( excel2 , excel2[title] = EARLIER( [title] ) ) ) , "UnCommon" , "Common" )

Use this New Column to visualize it using a Pie Chart

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

i tried above dax function, but it showing common correctly, but instead of uncommon, it is showing blank

Jameel_0-1701008674604.png

Jameel_1-1701008790792.png

 

 

Note: mentioned "most used" instead of  "common"

@Jameel 

Please share your PBI file with dummy data

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy i couldnt attach the PBI file.

Please see the below snapshots

 

sheet2sheet2sheet1sheet1PowerbiPowerbirelationshiprelationship

@Jameel 

Save the file in Google Drive and attach the link here and provide permission to access it.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Jameel ,

 

According to your statement, I think your issue should be caused that you add this column in Sheet1(2). Due to it doesn't contain all title in it, so it will return blank in your visual. Please add a column in Sheet1.

Column =
IF (
    ISEMPTY (
        FILTER ( 'Sheet1 (2)', 'Sheet1 (2)'[title] = EARLIER ( 'Sheet1'[title] ) )
    ),
    "UnCommon",
    "Common"
)

Result is as below.

vrzhoumsft_0-1701153143032.pngvrzhoumsft_1-1701153194695.png

 

Best Regards,
Rico Zhou

 

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

 

@v-rzhou-msft Its working..Thanks for the help

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.