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

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors