Forum Discussion

Do57792's avatar
Do57792
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Group by at matrix visula

Hello guys, 

 

the following problem occured:
In general there are 2 tables:

Table 1: Finishing date
                

HouseStagefinishing date
1101.Feb 2025
1205 Jan 2025
2503 March 2026
311 Sep 2025
345 Nov 2026



Table 3:

HouseStageInvoice
1199
1299
25100
31101
34102




Tabel 2: Invoice date

InvoiceInvoice Date
9901.Dec 2025
10405.Jan.2025
10003 March 2026
1021 Sep 2025
1011 Jan 2027

 

So now I could combine the tables and calculate the Differences between finishing date and invoice date at dax, which is working fine so far on House-Stage level. I could also combine the tables at the M query level, but this is not the prefered solution, or?
Only on house level I have a finishing date for every stage. The invoices could be one for the whole house but also more than one. 

 

 

HouseFinish dateInvoice dateIn time?
105 Jan 202501.Dec 2025no
203 March 202603 March 2026yes
31 Sep 20251 sep 2025no

 


For the dax topic my problem is the following: I would like to have a result matrix like this, where I can also select the houses and show the stage if it necessary.

HouseIn time?
1no
2yes
3no

If one invoice is delayed it should get a "no" (or a 0) only if every invoice for the whole house is in time there will be a "yes". 


Many thanks in advance.


How could I group this on "house level"?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Do57792 ,
    Thank you Cookistador for the prompt response!

    Thank you for reaching out to the Microsoft fabric community forum. 

    Here are the few steps which you may follow to resolve the issue :
    * Create a HouseBridge table with unique house numbers by removing duplicates from the House column in the Finishing Date table.
    * Establish a One-to-Many relationship between HouseBridge, Finishing Date, and Invoice tables using the House column, with cross filter direction set to Both.
    * Create a DAX measure to return "yes" if all invoices are on time, otherwise "no".

    In Time = 
VAR CurrentHouse = SELECTEDVALUE('InvoiceTable'[House])  -- Get the current house from the filter context
VAR Invoices = 
    CALCULATETABLE(
        'InvoiceTable',
        'InvoiceTable'[House] = CurrentHouse  -- Filter by current house
    )
VAR IsAnyInvoiceLate = 
    COUNTROWS(
        FILTER(
            Invoices,
            VAR FinishingDate = 
                LOOKUPVALUE(
                    'FinishingDateTable'[finishingdate],
                    'FinishingDateTable'[House], CurrentHouse,
                    'FinishingDateTable'[Stage], 'InvoiceTable'[Stage]  -- Ensure matching the correct stage for the house
                )
            VAR InvoiceDate = 
                LOOKUPVALUE(
                    'InvoiceDateTable'[InvoiceDate],
                    'InvoiceDateTable'[Invoice], 'InvoiceTable'[Invoice]  -- Ensure matching the correct invoice date
                )
            RETURN InvoiceDate > FinishingDate  -- If invoice date is later than the finishing date, consider it late
        )
    )
RETURN 
    IF(IsAnyInvoiceLate > 0, "no", "yes")

     

    Now you may create the report by adding a Table visual in your report and then include House from HouseBridge along with the In Time measure.

    Please find the attached file for your reference.
       
    Hope this helps!
    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

    Best Regards, 
    Harshitha.
    Community Support Team .

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Do57792 ,
    Thank you Cookistador for the prompt response!

    Thank you for reaching out to the Microsoft fabric community forum. 

    Here are the few steps which you may follow to resolve the issue :
    * Create a HouseBridge table with unique house numbers by removing duplicates from the House column in the Finishing Date table.
    * Establish a One-to-Many relationship between HouseBridge, Finishing Date, and Invoice tables using the House column, with cross filter direction set to Both.
    * Create a DAX measure to return "yes" if all invoices are on time, otherwise "no".

    In Time = 
VAR CurrentHouse = SELECTEDVALUE('InvoiceTable'[House])  -- Get the current house from the filter context
VAR Invoices = 
    CALCULATETABLE(
        'InvoiceTable',
        'InvoiceTable'[House] = CurrentHouse  -- Filter by current house
    )
VAR IsAnyInvoiceLate = 
    COUNTROWS(
        FILTER(
            Invoices,
            VAR FinishingDate = 
                LOOKUPVALUE(
                    'FinishingDateTable'[finishingdate],
                    'FinishingDateTable'[House], CurrentHouse,
                    'FinishingDateTable'[Stage], 'InvoiceTable'[Stage]  -- Ensure matching the correct stage for the house
                )
            VAR InvoiceDate = 
                LOOKUPVALUE(
                    'InvoiceDateTable'[InvoiceDate],
                    'InvoiceDateTable'[Invoice], 'InvoiceTable'[Invoice]  -- Ensure matching the correct invoice date
                )
            RETURN InvoiceDate > FinishingDate  -- If invoice date is later than the finishing date, consider it late
        )
    )
RETURN 
    IF(IsAnyInvoiceLate > 0, "no", "yes")

     

    Now you may create the report by adding a Table visual in your report and then include House from HouseBridge along with the In Time measure.

    Please find the attached file for your reference.
       
    Hope this helps!
    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

    Best Regards, 
    Harshitha.
    Community Support Team .

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Do57792,
      Just wanted to check if you had the opportunity to review the suggestion provided?
      If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

      Thank You.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Do57792,

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.


        Thank you.

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    1 year ago

    Hello Do57792,

     

    The following dax measure should return what you need

     

    In time? =
    VAR CurrentHouse = SELECTEDVALUE('Table3'[House]) // Gets the house from the current filter context (e.g., row in your matrix)
     
    VAR IsAnyInvoiceLate =
        COUNTROWS(
            FILTER(
                CALCULATETABLE(
                    'Table3',
                    'Table3'[House] = CurrentHouse
                ),
                VAR FinishingDate = LOOKUPVALUE(
                                      'Finishing date'[finishing date],
                                      'Finishing date'[House], 'Table3'[House],
                                      'Finishing date'[Stage], 'Table3'[Stage]
                                  )
                VAR InvoiceActualDate = LOOKUPVALUE(
                                           'Invoice date'[Invoice Date],
                                           'Invoice date'[Invoice], 'Table3'[Invoice]
                                       )
                RETURN InvoiceActualDate > FinishingDate
            )
        ) 

    RETURN
        IF(
            IsAnyInvoiceLate,
            "No",
            "Yes" 
        )
     
    This is the result I get:
     

     

  • saritasw's avatar
    saritasw
    Icon for Resolver II rankResolver II
    1 year ago

    Do57792 

    1. To keep things maintainable and DAX-friendly, structure your data like this:

    Fact Table: FactInvoices
    Contains transactional records:

    House

    Stage

    Invoice

    Dimension Table: DimHouseStage
    Contains metadata for each stage:

    House

    Stage

    Finishing Date

    Create a relationship from FactInvoices[House, Stage] to DimHouseStage[House, Stage].

    Dimension Table: DimInvoice
    Contains invoice metadata:

    Invoice

    Invoice Date

    Create a relationship from FactInvoices[Invoice] to DimInvoice[Invoice].

    This structure creates a proper star schema, which simplifies DAX and supports better performance and slicing.

    2. Create a DAX measure to evaluate if all stages for a house are invoiced on time.
    - Create a virtual table InvoicedStages using ADDCOLUMNS.

    - Pull finishing date from DimHouseStage and  invoice date from DimInvoice using RELATED

    - Add a column IsInTime:
    1 if invoice date ≤ finishing date, otherwise 0

    - FILTER this table to COUNTROWS where IsInTime = 0 → meaning delays

    IF even one delay exists, return "no"
    IF all invoices are on time, return "yes"

     


    *****************************************************************************************************************************************************

    If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

    Thank you,
    Sarita 
    Linkedin 

  • Do57792's avatar
    Do57792
    Icon for Advocate I rankAdvocate I
    1 year ago

    Hello thank you a lot. It is already accepted as solution.