Forum Discussion

scd66's avatar
scd66
Icon for Helper I rankHelper I
1 year ago
Solved

Replace repeating row values with blank in a Table Visual

I have a table visual in PowerBI shown below where I have split total room areas into percentages of room owner where the room is shared. Is there a formula that could be written to replace selected ...
Need Help
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    Hi scd66 ,

    As per your data context i have reframed the measure, please try the below DAX measure, make sure you are using field names correctly and let me know if it works.

    Capacity Supp =
    IF ('Locations UDFs'[Room ID Code] = CALCULATE (MIN ( 'Locations UDFs'[Room ID Code] ),
    FILTER ('Locations UDFs','Locations UDFs'[Room ID Code] = EARLIER ( 'Locations UDFs'[Room ID Code] ))),
    'Locations UDFs'[Capacity],BLANK())

    Regards,
    Chaithra

v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
1 year ago

HI scd66 ,

May I ask if you have gotten this issue resolved?

 

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

 

Regards,
Chaithra.

  • scd66's avatar
    scd66
    Icon for Helper I rankHelper I
    1 year ago

    Hi,

     

    Sorry I tried your latest formula as a new column but it still said I had a syntax error and it wouldn't run.

    Regards,

    scd66

    • v-echaithra's avatar
      v-echaithra
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi scd66 ,

      Please look into the pbix file attatched, where we tried to reproduce the scenario with your sample data.
      Compare the Location Notes with Location Notes Display and Functional Suitability with Functional Display.

      You can try these below DAX measures to build calculated columns i.e, Location Notes Display and Functional Display.

      For Index Column:

      Index =
      RANKX(
      ALL('Locations UDFs'),
      'Locations UDFs'[Room ID Code]
      & "|" & 'Locations UDFs'[Room Function]
      & "|" & 'Locations UDFs'[Activity Tag]
      & "|" & FORMAT('Locations UDFs'[% Activity Tag], "0.00"),
      ,
      ASC,
      DENSE
      )


      For Capacity Display Column:

      Capacity Display =
      VAR CurrentRoomID = 'Locations UDFs'[Room ID Code]
      VAR MinRow =
      CALCULATE (
      MIN ( 'Locations UDFs'[Index] ),
      FILTER ( 'Locations UDFs', 'Locations UDFs'[Room ID Code] = CurrentRoomID )
      )
      RETURN
      IF ( 'Locations UDFs'[Index] = MinRow, 'Locations UDFs'[Capacity], BLANK() )

      For Location Notes Display:

      Location Notes Display =
      IF(
      ISBLANK('Locations UDFs'[Capacity Display]),
      BLANK(),
      'Locations UDFs'[Location Notes]
      )

      For Functional Display column:

      Functional Display =
      IF(
      ISBLANK('Locations UDFs'[Capacity Display]),
      BLANK(),
      'Locations UDFs'[Functional Suitability]
      )

      If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

      Regards,
      Chaithra.



      • scd66's avatar
        scd66
        Icon for Helper I rankHelper I
        1 year ago

        Thanks but your attachment won't open on my laptop. It produces the following message: