Skip to main content
cancel
Showing results for 
Search instead 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

Reply

Unable to get distinct row id for the table (Power App Visual & Power BI Desktop)

Hello Power BI enthusiasts,

 

This is the YouTube Tutorial I followed to create a Power App. 

https://www.youtube.com/watch?v=-dRDKbm-v6Y 

The step where I'm stuck is the Row ID for my multi-category table.

Note: Also why do I have to use this workaround this Data Appended table I'm using is from Excel and write the changes back to excel.

 

Then I used these 2 tutorials to calculate the row ID:

Index = CALCULATE(DISTINCTCOUNT('End Market Projections'[Geography]), FILTER('End Market Projections', 'End Market Projections'[WRK Parent node]=EARLIER('End Market Projections'[WRK Parent node]) && 'End Market Projections'[Geography]<=EARLIER('End Market Projections'[Geography])))
 
Row ID = RANKX(FILTER('End Market Projections', 'End Market Projections'[WRK Parent node] = EARLIER('End Market Projections'[WRK Parent node])), 'End Market Projections'[WRK parent category ])

 

I faced a few problems:

Problem 1: You can see the problem that the Row ID is not unique for the categories.

I want the expected row numbers to be like just a unique 1-3 digit number.

 

Screenshot 2022-06-11 144719.png

 

Problem 2: Also, the most important aspect of this app is to show the 'WRK CAGR Est.' beside the CAGR '21-25. But when I add this to the existing table it replicates the table entries into 2. 

Screenshot 2022-06-11 145706.png

 

PLEASE TRY TO FIND A SOLUTION TO THIS PROBLEM!!

 

I'm sharing the .pbix file that has the OG report:

https://drive.google.com/file/d/1vWWg52y7WC7_CYgkatbj_5eaMx7soLZt/view?usp=sharing 

 

P.S.: I will be very grateful if you are able to provide some solution/guide to this problem before Monday. I have to show this to my client.

1 ACCEPTED SOLUTION

Thank you for spending your time creating a solution. But, in this case, this is not useful for me, because there has to be a relationship between table 'wrk cagr' and table 'End Market Projection' for the 'Row ID' fields.

 

Anyways I was able to find a solution. I'm attaching the .pbix file for anyone who wants to see for themselves.

 

https://drive.google.com/file/d/1nRa7FE7uhnyf4zNHMjtj9g6tvhrtd4ti/view?usp=sharing

 

What did I do to solve this?

Note: For numbers 1 & 2 I followed the tutorials mentioned in my question above.

  1. Create Index for the Data: 
    Index = CALCULATE(DISTINCTCOUNT('End Market Projections'[WRK Parent node]), FILTER('End Market Projections', 'End Market Projections'[WRK Parent node]<=EARLIER('End Market Projections'[WRK Parent node])))
  2. Create Row Number for the subcategories for your Data:
    Row Number = RANKX(FILTER('End Market Projections', 'End Market Projections'[WRK Parent node] = EARLIER('End Market Projections'[WRK Parent node])), 'End Market Projections'[2021])
  3. Concatenate both the Index and Row ID to create a unique row number for each row of your data:
    Row ID = CONCATENATE('End Market Projections'[Row Number], 'End Market Projections'[Index])

In this way I was able to have to be a relationship between table 'wrk cagr' and table 'End Market Projection' for the 'Row ID' fields. 

 

Final Table Look:

Screenshot 2022-06-16 135604.png

 

 

Warm regards, 

Priyanka Jha.

 

View solution in original post

2 REPLIES 2
v-easonf-msft
Community Support
Community Support

Hi, @PriyankaJhaTheA 

1. You can try formulas as below to add the new "Row ID"

M_RowID = MAX('End Market Projections'[Row ID])
New_row_number = 
VAR _RowID = 'End Market Projections'[M_RowID]
RETURN
    IF (
        HASONEVALUE ( 'End Market Projections'[WRK Parent node] )
            && ( _RowID > 0 ),
        VAR RankedParentnode =
            ADDCOLUMNS (
                ALLSELECTED ( 'End Market Projections' ),
                "@Parentnode Ranked",
                    RANKX (
                        ALLSELECTED ( 'End Market Projections' ),
                        'End Market Projections'[WRK Parent node],
                        ,
                        ASC,
                        DENSE
                    )
            )
        VAR MaxRanked =
            MAXX ( RankedParentnode , [@Parentnode Ranked] )
        VAR LookupTable =
            ADDCOLUMNS (
                RankedParentnode,
                "@RowID",
                    'End Market Projections'[M_RowID] * MaxRanked + [@Parentnode Ranked]
            )
        VAR CurrentParentnode =
            SELECTEDVALUE ( 'End Market Projections'[WRK Parent node] )
        VAR CurrentParentnodeRanked =
            RANKX (
                ALLSELECTED ( 'End Market Projections' ),
                'End Market Projections'[WRK Parent node],
                CurrentParentnode,
                ASC,
                DENSE
            )
        VAR CurrentValue = _RowID * MaxRanked + CurrentParentnodeRanked
        VAR Ranking =
            RANKX ( LookupTable, [@RowID], CurrentValue, ASC, DENSE )
        RETURN
            Ranking
    )

 

2. You may need to delete the relationship between table 'wrk cagr' and table 'End Market Projection'.

veasonfmsft_0-1655173966709.png

result:

veasonfmsft_0-1655188514074.png

 

Best Regards,
Community Support Team _ Eason

Thank you for spending your time creating a solution. But, in this case, this is not useful for me, because there has to be a relationship between table 'wrk cagr' and table 'End Market Projection' for the 'Row ID' fields.

 

Anyways I was able to find a solution. I'm attaching the .pbix file for anyone who wants to see for themselves.

 

https://drive.google.com/file/d/1nRa7FE7uhnyf4zNHMjtj9g6tvhrtd4ti/view?usp=sharing

 

What did I do to solve this?

Note: For numbers 1 & 2 I followed the tutorials mentioned in my question above.

  1. Create Index for the Data: 
    Index = CALCULATE(DISTINCTCOUNT('End Market Projections'[WRK Parent node]), FILTER('End Market Projections', 'End Market Projections'[WRK Parent node]<=EARLIER('End Market Projections'[WRK Parent node])))
  2. Create Row Number for the subcategories for your Data:
    Row Number = RANKX(FILTER('End Market Projections', 'End Market Projections'[WRK Parent node] = EARLIER('End Market Projections'[WRK Parent node])), 'End Market Projections'[2021])
  3. Concatenate both the Index and Row ID to create a unique row number for each row of your data:
    Row ID = CONCATENATE('End Market Projections'[Row Number], 'End Market Projections'[Index])

In this way I was able to have to be a relationship between table 'wrk cagr' and table 'End Market Projection' for the 'Row ID' fields. 

 

Final Table Look:

Screenshot 2022-06-16 135604.png

 

 

Warm regards, 

Priyanka Jha.

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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