Forum Discussion

MarkBurgess's avatar
MarkBurgess
Advocate I
1 year ago
Solved

Modelling options for multiple yes/no fields

We have a lakehouse table in our directly lake semantic model that has 5 flag fields containing a 1 or 0 for True/False respectively. We would like to keep them as integers as they are easier to aggregate (e.g. sum/count for percentage), but there is also a requirement to include the fields in a slicer and in this case a Y/N or even Yes/No would be preferable. What is the most efficient way to deal with this sort of thing.

Options considered:

  1. Replacing values with Y/N and then adjusting the DAX accordingly – measure become needlessly cumbersome.
  2. Create a DimYN table that maps 1/0 to T/F and create multiple role playing dimensions from it – can’t base a calculated table off a direct lake table.
  3. Multiple relationships to the same table and using USERELATIONSHIP DAX – but not sure if that helps for the slicers.
  4. Create 5 separate physical DimYN tables in the lakehouse for each of the 5 fields – creating 5 identical (but tiny) tables and 5 relationships.
  5. Create two versions of each field in the table.

I’m veering towards 4 or 5, but wanted to get some other takes on this problem and check I’m not missing anything.

Thanks

  • MarkBurgess I think that both 4 and 5 look good. The simplest is the two versions of each field. If it doesn't blow up your data model that's probably a good way to go. That said, 4 probably keeps the spirit of the star schema.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    MarkBurgess I think that both 4 and 5 look good. The simplest is the two versions of each field. If it doesn't blow up your data model that's probably a good way to go. That said, 4 probably keeps the spirit of the star schema.

    • MarkBurgess's avatar
      MarkBurgess
      Advocate I

      Thanks Greg_Deckler , the separate tables give users a choice between Y/N and Yes/No so might just bite the bullet and treat them as 'proper' dimensions. Appriciate your input.